const { pool } = require('../config/db');
const llmService = require('./llmService');
const { downloadImage } = require('../utils/fileUtils');
const config = require('../config/config');
const path = require('path');

/**
 * 数据处理服务
 */
class ProcessService {
  /**
   * 格式化日期（处理不完整的日期格式）- 用于需要完整日期的字段
   * @param {string} dateStr - 日期字符串
   * @returns {string|null} - 格式化后的日期或null
   */
  formatDate(dateStr) {
    if (!dateStr || typeof dateStr !== 'string') {
      return null;
    }

    // 去除空格
    dateStr = dateStr.trim();

    // 完整日期格式 YYYY-MM-DD
    if (/^\d{4}-\d{2}-\d{2}$/.test(dateStr)) {
      return dateStr;
    }

    // 年月格式 YYYY-MM，补全为当月1号
    if (/^\d{4}-\d{2}$/.test(dateStr)) {
      return `${dateStr}-01`;
    }

    // 只有年份 YYYY，补全为当年1月1号
    if (/^\d{4}$/.test(dateStr)) {
      return `${dateStr}-01-01`;
    }

    // 其他格式尝试解析
    try {
      const date = new Date(dateStr);
      if (!isNaN(date.getTime())) {
        const year = date.getFullYear();
        const month = String(date.getMonth() + 1).padStart(2, '0');
        const day = String(date.getDate()).padStart(2, '0');
        return `${year}-${month}-${day}`;
      }
    } catch (error) {
      console.warn(`无法解析日期: ${dateStr}`);
    }

    return null;
  }

  /**
   * 规范化日期（保持原始精度，不自动补全）
   * @param {string} dateStr - 日期字符串
   * @returns {string|null} - 规范化后的日期或null
   */
  normalizeDate(dateStr) {
    if (!dateStr || typeof dateStr !== 'string') {
      return null;
    }

    // 去除空格和多余字符
    dateStr = dateStr.trim();

    // 完整日期格式 YYYY-MM-DD（保持不变）
    if (/^\d{4}-\d{2}-\d{2}$/.test(dateStr)) {
      return dateStr;
    }

    // 年月格式 YYYY-MM（保持不变）
    if (/^\d{4}-\d{2}$/.test(dateStr)) {
      return dateStr;
    }

    // 只有年份 YYYY（保持不变，不补全）
    if (/^\d{4}$/.test(dateStr)) {
      return dateStr;
    }

    // 其他格式尝试解析并转换为适当格式
    try {
      // 尝试解析各种日期格式
      const date = new Date(dateStr);
      if (!isNaN(date.getTime())) {
        const year = date.getFullYear();
        const month = String(date.getMonth() + 1).padStart(2, '0');
        const day = String(date.getDate()).padStart(2, '0');
        return `${year}-${month}-${day}`;
      }
    } catch (error) {
      console.warn(`无法解析日期: ${dateStr}`);
    }

    return null;
  }

  /**
   * 获取待处理的原始人员记录（支持分页）
   * @param {number} page - 页码（从1开始）
   * @param {number} pageSize - 每页数量
   * @returns {Promise<Object>} - 待处理的记录列表和总数
   */
  async getPendingRecords(page = 1, pageSize = 20) {
    try {
      // 获取总数
      const [countResult] = await pool.execute(
        'SELECT COUNT(*) as total FROM raw_personnel WHERE process_status = ?',
        ['pending']
      );
      const total = countResult[0].total;

      // 获取分页数据
      const offset = (page - 1) * pageSize;
      const [rows] = await pool.execute(
        'SELECT * FROM raw_personnel WHERE process_status = ? ORDER BY id DESC LIMIT ? OFFSET ?',
        ['pending', pageSize, offset]
      );

      return {
        data: rows,
        total: total,
        page: page,
        pageSize: pageSize,
        totalPages: Math.ceil(total / pageSize)
      };
    } catch (error) {
      console.error('获取待处理记录失败:', error.message);
      throw error;
    }
  }

  /**
   * 获取待处理记录（兼容旧接口）
   * @param {number} limit - 获取数量
   * @returns {Promise<Array>} - 待处理的记录列表
   */
  async getPendingRecordsList(limit = 10) {
    try {
      const [rows] = await pool.execute(
        'SELECT * FROM raw_personnel WHERE process_status = ? ORDER BY id DESC LIMIT ?',
        ['pending', limit]
      );
      return rows;
    } catch (error) {
      console.error('获取待处理记录失败:', error.message);
      throw error;
    }
  }

  /**
   * 更新原始记录的处理状态
   * @param {number} id - 记录ID
   * @param {string} status - 状态
   * @param {string} errorMsg - 错误信息（可选）
   */
  async updateProcessStatus(id, status, errorMsg = null) {
    try {
      const sql = `
        UPDATE raw_personnel 
        SET process_status = ?, process_time = NOW(), process_error = ?
        WHERE id = ?
      `;
      await pool.execute(sql, [status, errorMsg, id]);
    } catch (error) {
      console.error('更新处理状态失败:', error.message);
      throw error;
    }
  }

  /**
   * 保存处理后的人员信息
   * @param {number} rawPersonnelId - 原始人员ID
   * @param {Object} processedData - 处理后的数据
   */
  async saveProcessedData(rawPersonnelId, processedData) {
    try {
      const sql = `
        INSERT INTO processed_personnel (
          raw_personnel_id, country_region, chinese_name, english_name,
          gender, birth_date, age, email, phone,
          graduate_school, education_level, military_branch, military_rank,
          organization, current_position, position_start_date,
          avatar_path, english_biography, chinese_biography,
          work_experience_cn, education_experience_cn,
          work_status, data_source, source_data_id
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `;

      await pool.execute(sql, [
        rawPersonnelId,
        processedData.country_region,
        processedData.chinese_name,
        processedData.english_name,
        processedData.gender,
        processedData.birth_date,
        processedData.age,
        processedData.email,
        processedData.phone,
        processedData.graduate_school,
        processedData.education_level,
        processedData.military_branch,
        processedData.military_rank,
        processedData.organization,
        processedData.current_position,
        processedData.position_start_date,
        processedData.avatar_path,
        processedData.english_biography,
        processedData.chinese_biography,
        processedData.work_experience_cn,
        processedData.education_experience_cn,
        processedData.work_status,
        processedData.data_source,
        processedData.source_data_id
      ]);
    } catch (error) {
      console.error('保存处理后数据失败:', error.message);
      throw error;
    }
  }

  /**
   * 处理单条人员记录
   * @param {Object} record - 原始人员记录
   * @returns {Promise<Object>} - 处理结果
   */
  async processSingleRecord(record) {
    try {
      console.log(`开始处理人员: ${record.name} (ID: ${record.id})`);

      // 更新状态为处理中
      await this.updateProcessStatus(record.id, 'processing');

      // 1. 先翻译简介为双语（如果是英文，优先翻译为中文用于后续提取）
      console.log('- 翻译简介为双语...');
      const biography = await llmService.translateToBothLanguages(record.biography || '');
      
      // 检测原始简介语言
      const originalIsEnglish = !/[\u4e00-\u9fa5]/.test(record.biography || '');
      
      // 2. 使用中文简介进行信息提取（提高准确性）
      console.log('- 调用LLM提取信息（使用中文简介）...');
      const biographyForExtraction = biography.chinese || record.biography || '';
      
      // 构建上下文信息（如果简介中未提及当前职务，可以从数据源中获取）
      const extractionContext = {};
      if (record.position && record.position.trim()) {
        extractionContext.position = record.position;
      }
      if (record.rank && record.rank.trim()) {
        extractionContext.rank = record.rank;
      }
      
      const extractedInfo = await llmService.extractPersonnelInfo(biographyForExtraction, extractionContext);

      // 3. 翻译姓名为中文（如果 LLM 没有提取到中文名）
      let chineseName = extractedInfo.chinese_name;
      if (!chineseName || !/[\u4e00-\u9fa5]/.test(chineseName)) {
        // 如果没有中文名或中文名中没有中文字符，则翻译英文名
        if (extractedInfo.english_name || record.name) {
          const nameToTranslate = extractedInfo.english_name || record.name;
          console.log(`- 翻译姓名为中文: ${nameToTranslate}...`);
          chineseName = await llmService.translateToChinese(nameToTranslate);
        } else {
          chineseName = record.name;
        }
      }

      // 4. 翻译工作经历（如果LLM提取的是英文，则翻译为中文）
      console.log('- 处理工作经历...');
      let workExperienceCn = null;
      if (extractedInfo.work_experience && Array.isArray(extractedInfo.work_experience)) {
        // 过滤掉内容为空的记录（只保留有实质内容的）
        const validWorkExperience = extractedInfo.work_experience.filter(exp => {
          const hasContent = (exp.organization && exp.organization.trim()) || 
                           (exp.position && exp.position.trim());
          return hasContent;
        });
        
        if (validWorkExperience.length > 0) {
          const workExpStr = validWorkExperience
            .map(exp => `${exp.time || ''} ${exp.organization || ''} ${exp.position || ''}`.trim())
            .join('\n');
          // 如果原始简介是英文，LLM提取的也是英文，需要翻译
          // 如果原始简介是中文，LLM提取的也是中文，无需翻译
          const needsTranslation = originalIsEnglish && !/[\u4e00-\u9fa5]/.test(workExpStr);
          workExperienceCn = needsTranslation ? await llmService.translateToChinese(workExpStr) : workExpStr;
        }
      }

      // 5. 翻译教育经历（如果LLM提取的是英文，则翻译为中文）
      console.log('- 处理教育经历...');
      let educationExperienceCn = null;
      if (extractedInfo.education_experience && Array.isArray(extractedInfo.education_experience)) {
        // 过滤掉内容为空的记录（只保留有实质内容的）
        const validEducationExperience = extractedInfo.education_experience.filter(exp => {
          const hasContent = (exp.school && exp.school.trim()) || 
                           (exp.major && exp.major.trim());
          return hasContent;
        });
        
        if (validEducationExperience.length > 0) {
          const eduExpStr = validEducationExperience
            .map(exp => `${exp.time || ''} ${exp.school || ''} ${exp.major || ''}`.trim())
            .join('\n');
          // 如果原始简介是英文，LLM提取的也是英文，需要翻译
          // 如果原始简介是中文，LLM提取的也是中文，无需翻译
          const needsTranslation = originalIsEnglish && !/[\u4e00-\u9fa5]/.test(eduExpStr);
          educationExperienceCn = needsTranslation ? await llmService.translateToChinese(eduExpStr) : eduExpStr;
        }
      }

      // 6. 翻译关键字段为中文（使用领域特定翻译映射）
      console.log('- 翻译关键字段为中文...');
      const [
        militaryRankCn,
        militaryBranchCn,
        currentPositionCn,
        workStatusCn,
        organizationCn,
        educationLevelCn,
        graduateSchoolCn
      ] = await Promise.all([
        llmService.translateField(extractedInfo.military_rank || record.rank),
        llmService.translateField(extractedInfo.military_branch),
        llmService.translateField(extractedInfo.current_position || record.position),
        llmService.translateField(extractedInfo.work_status),
        llmService.translateField(extractedInfo.organization),
        llmService.translateField(extractedInfo.education_level),
        llmService.translateField(extractedInfo.graduate_school)
      ]);

      // 7. 下载头像
      console.log('- 下载头像...');
      let avatarPath = null;
      if (record.avatar_url) {
        try {
          avatarPath = await downloadImage(
            record.avatar_url,
            path.resolve(config.upload.avatarDir)
          );
        } catch (error) {
          console.warn('头像下载失败:', error.message);
        }
      }

      // 8. 组装处理后的数据
      const processedData = {
        country_region: extractedInfo.country_region,
        chinese_name: chineseName || record.name,  // 使用翻译后的中文名
        english_name: extractedInfo.english_name || record.name,
        gender: extractedInfo.gender,
        birth_date: this.normalizeDate(extractedInfo.birth_date),  // 使用normalizeDate保持原始精度
        age: extractedInfo.age,
        email: extractedInfo.email,
        phone: extractedInfo.phone,
        graduate_school: graduateSchoolCn,  // LLM直接提取并翻译的毕业院校
        education_level: educationLevelCn,  // LLM直接提取并翻译的最高学历
        military_branch: militaryBranchCn,  // 使用翻译后的军兵种
        military_rank: militaryRankCn,  // 使用翻译后的军衔
        organization: organizationCn,  // 使用翻译后的所在机构
        current_position: currentPositionCn,  // 使用翻译后的职务
        position_start_date: this.normalizeDate(extractedInfo.position_start_date),  // 使用normalizeDate保持原始精度
        avatar_path: avatarPath,
        english_biography: biography.english,
        chinese_biography: biography.chinese,
        work_experience_cn: workExperienceCn,
        education_experience_cn: educationExperienceCn,
        work_status: workStatusCn,  // 使用翻译后的工作状态
        data_source: record.data_source,
        source_data_id: record.id.toString()
      };

      // 9. 保存处理后的数据
      console.log('- 保存处理后数据...');
      await this.saveProcessedData(record.id, processedData);

      // 10. 更新原始记录状态为已完成
      await this.updateProcessStatus(record.id, 'completed');

      console.log(`✓ 处理完成: ${record.name}`);

      return {
        success: true,
        recordId: record.id,
        name: record.name
      };
    } catch (error) {
      console.error(`✗ 处理失败: ${record.name}`, error.message);

      // 更新状态为失败
      await this.updateProcessStatus(record.id, 'failed', error.message);

      return {
        success: false,
        recordId: record.id,
        name: record.name,
        error: error.message
      };
    }
  }

  /**
   * 批量处理人员记录
   * @param {number} batchSize - 每批处理数量
   * @returns {Promise<Object>} - 处理结果统计
   */
  async processBatch(batchSize = 10) {
    try {
      console.log(`\n========== 开始批量处理 (批次大小: ${batchSize}) ==========\n`);

      // 获取待处理记录
      const records = await this.getPendingRecordsList(batchSize);

      if (records.length === 0) {
        console.log('没有待处理的记录');
        return {
          success: true,
          message: '没有待处理的记录',
          totalCount: 0,
          successCount: 0,
          failCount: 0
        };
      }

      console.log(`获取到 ${records.length} 条待处理记录\n`);

      // 逐条处理
      const results = [];
      for (const record of records) {
        const result = await this.processSingleRecord(record);
        results.push(result);
      }

      // 统计结果
      const successCount = results.filter(r => r.success).length;
      const failCount = results.filter(r => !r.success).length;

      console.log('\n========== 批量处理完成 ==========');
      console.log(`总数: ${records.length}`);
      console.log(`成功: ${successCount}`);
      console.log(`失败: ${failCount}`);
      console.log('==================================\n');

      return {
        success: true,
        totalCount: records.length,
        successCount,
        failCount,
        details: results
      };
    } catch (error) {
      console.error('批量处理失败:', error.message);
      throw error;
    }
  }

  /**
   * 处理所有待处理记录
   * @param {number} batchSize - 每批处理数量
   */
  async processAll(batchSize = 10) {
    try {
      console.log('\n========== 开始处理所有待处理记录 ==========');
      console.log(`批次大小: ${batchSize}`);
      console.log('===========================================\n');

      let hasMore = true;
      let totalProcessed = 0;
      let totalSuccess = 0;
      let totalFail = 0;
      let batchCount = 0;
      let maxBatches = 1000; // 防止无限循环，最多处理1000批

      while (hasMore && batchCount < maxBatches) {
        batchCount++;
        console.log(`\n📦 开始处理第 ${batchCount} 批...`);
        
        const result = await this.processBatch(batchSize);
        
        if (result.totalCount === 0) {
          console.log('✓ 没有更多待处理记录，处理完成');
          hasMore = false;
        } else {
          totalProcessed += result.totalCount;
          totalSuccess += result.successCount;
          totalFail += result.failCount;
          
          console.log(`✓ 第 ${batchCount} 批处理完成`);
          console.log(`   本批: ${result.totalCount} 条 (成功: ${result.successCount}, 失败: ${result.failCount})`);
          console.log(`   累计: ${totalProcessed} 条 (成功: ${totalSuccess}, 失败: ${totalFail})`);
        }
      }

      if (batchCount >= maxBatches) {
        console.warn(`\n⚠️  已达到最大批次限制 (${maxBatches})，停止处理`);
      }

      console.log('\n========== 全部处理完成 ==========');
      console.log(`总批次数: ${batchCount}`);
      console.log(`总处理数: ${totalProcessed}`);
      console.log(`总成功数: ${totalSuccess}`);
      console.log(`总失败数: ${totalFail}`);
      console.log('==================================\n');

      return {
        success: true,
        batchCount,
        totalProcessed,
        totalSuccess,
        totalFail
      };
    } catch (error) {
      console.error('处理所有记录失败:', error.message);
      throw error;
    }
  }

  /**
   * 删除待处理记录
   * @param {number} id - 记录ID
   * @returns {Promise<Object>} - 删除结果
   */
  async deleteRecord(id) {
    try {
      console.log(`删除待处理记录: ID ${id}`);

      // 检查记录是否存在且状态为pending
      const [rows] = await pool.execute(
        'SELECT * FROM raw_personnel WHERE id = ?',
        [id]
      );

      if (rows.length === 0) {
        return {
          success: false,
          message: '记录不存在'
        };
      }

      const record = rows[0];

      // 只允许删除待处理的记录
      if (record.process_status !== 'pending') {
        return {
          success: false,
          message: `无法删除：该记录状态为 ${record.process_status}，只能删除待处理的记录`
        };
      }

      // 执行删除
      await pool.execute(
        'DELETE FROM raw_personnel WHERE id = ?',
        [id]
      );

      console.log(`✓ 记录删除成功: ${record.name} (ID: ${id})`);

      return {
        success: true,
        message: '删除成功',
        name: record.name,
        id: id
      };
    } catch (error) {
      console.error('删除记录失败:', error.message);
      throw error;
    }
  }

  /**
   * 获取处理统计信息
   * @returns {Promise<Object>} - 统计信息
   */
  async getProcessStatistics() {
    try {
      // 获取各状态的数量
      const [rows] = await pool.execute(`
        SELECT 
          COUNT(*) as total,
          SUM(CASE WHEN process_status = 'pending' THEN 1 ELSE 0 END) as pending,
          SUM(CASE WHEN process_status = 'completed' THEN 1 ELSE 0 END) as completed,
          SUM(CASE WHEN process_status = 'failed' THEN 1 ELSE 0 END) as failed,
          SUM(CASE WHEN process_status = 'processing' THEN 1 ELSE 0 END) as processing
        FROM raw_personnel
      `);

      const stats = rows[0];
      const total = parseInt(stats.total) || 0;
      const pending = parseInt(stats.pending) || 0;
      const completed = parseInt(stats.completed) || 0;
      const failed = parseInt(stats.failed) || 0;
      const processing = parseInt(stats.processing) || 0;

      // 已处理 = 已完成 + 失败
      const processed = completed + failed;
      
      // 计算完成百分比
      const percentage = total > 0 ? Math.round((processed / total) * 100) : 0;

      return {
        total,
        pending,
        completed,
        failed,
        processing,
        processed,
        percentage
      };
    } catch (error) {
      console.error('获取处理统计失败:', error.message);
      throw error;
    }
  }

  /**
   * 修复头像问题
   * @param {number} batchSize - 每批修复数量（可选）
   * @returns {Promise<Object>} - 修复结果
   */
  async fixAvatars(batchSize = null) {
    try {
      const AvatarFixer = require('../scripts/fixAvatars');
      const fixer = new AvatarFixer();
      return await fixer.fixAll(batchSize);
    } catch (error) {
      console.error('修复头像失败:', error.message);
      throw error;
    }
  }

  /**
   * 获取头像统计信息
   * @returns {Promise<Object>} - 头像统计信息
   */
  async getAvatarStatistics() {
    try {
      const AvatarFixer = require('../scripts/fixAvatars');
      const fixer = new AvatarFixer();
      // 使用静默模式，不打印详细日志
      return await fixer.getStatistics(true);
    } catch (error) {
      console.error('获取头像统计失败:', error.message);
      throw error;
    }
  }
}

module.exports = new ProcessService();

